﻿CREATE PROCEDURE [dbo].[proc_Statistics_ReportCreate]
	(
		@CompanyId int,
		@ShengChengDate nvarchar(10)
	)
AS
Begin
	--核算昨日合计
	Declare @AllRecord int , @Total decimal(18,2)
	Select @AllRecord=Count(Id) From Finance_Report Where CompanyId=@CompanyId
	if(@AllRecord=0)
		begin
			Set @Total=ISNULL((Select Sum(Income) From Finance_RunningAccount Where CompanyId=@CompanyId ),0)
			+ISNULL((Select Sum(Prices) From Finance_Balance Where  CompanyId=@CompanyId And TypeId=1),0)
			-ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=@CompanyId),0)
			-ISNULL((Select Sum(Prices) From Finance_Balance Where  CompanyId=@CompanyId And TypeId=2),0)
		end
	else
		begin
			Set @Total=(Select Total From Finance_Report Where CompanyId=@CompanyId And ShengChengDate=dateadd(day,-1,Cast(@ShengChengDate as DateTime)))
		end

	--获取当日日报是否生成
	Declare @RecordCount int
	Select @RecordCount=Count(Id) From Finance_Report Where CompanyId=@CompanyId and Convert(nchar(10),ShengChengDate,120)=@ShengChengDate
	
	--插入及更新操作
	if(@RecordCount=0)
		begin
			Insert	Into Finance_Report
			(
			 CompanyId
			,CompanyName
			,CashIn
			,CashOut
			,BankIn
			,BankOut
			,PingZhengIn
			,PingZhengOut
			,XZML
			,XFML
			,XZYS
			,CostPriceAll
			,CostPriceFinish
			,WKSR
			,CreateDate
			,ShengChengDate
			)
			Select
			 c.Id
			,c.CompanyName
			,ISNULL((Select Sum(Income) From Finance_RunningAccount Where CompanyId=c.Id And PaymentTypeId=1 And Convert(nchar(10),TradeDate,120)=@ShengChengDate),0) As CashIn--现金收入
			,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id And PaymentTypeId=1 And Convert(nchar(10),TradeDate,120)=@ShengChengDate),0) As CashOut--现金支出
			,ISNULL((Select Sum(Income) From Finance_RunningAccount Where CompanyId=c.Id And PaymentTypeId<>1 And Convert(nchar(10),TradeDate,120)=@ShengChengDate),0) As BankIn--银行收入
			,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id And PaymentTypeId<>1 And Convert(nchar(10),TradeDate,120)=@ShengChengDate),0) As BankOut--银行支出
			,ISNULL((Select Sum(Prices) From Finance_Balance Where CompanyId=c.Id And TypeId=1 And Convert(nchar(10),CurrentDate,120)=@ShengChengDate),0) As PingZhengIn--平整增加
			,ISNULL((Select Sum(Prices) From Finance_Balance Where CompanyId=c.Id And TypeId=2 And Convert(nchar(10),CurrentDate,120)=@ShengChengDate),0) As PingZhengIn--平整减少
			,ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=c.Id And TypeId=1 And Convert(nchar(10),CreateDate,120)=@ShengChengDate),0) As XZML--新增毛利
			,ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=c.Id And TypeId=2 And Convert(nchar(10),CreateDate,120)=@ShengChengDate),0) As XFML--续费毛利
			,ISNULL((Select Sum(YingShouPrice-ShiShouPrice) From [Order] Where CompanyId=c.Id And Convert(nchar(10),TradeDate,120)=@ShengChengDate),0) As XZYS--新增应收
			,ISNULL((Select Sum(CostPrice) From Project Where CompanyId=c.Id  And  Convert(nchar(10),CreateDate,120)=@ShengChengDate),0) As CostPriceAll--新增项目成本
			,ISNULL((Select Sum(CostPrice) From Project Where CompanyId=c.Id  And  Convert(nchar(10),CreateDate,120)=@ShengChengDate And IsFinish=1),0) As CostPriceFinish--已完成项目成本
			,ISNULL((Select Sum(Income) From Finance_RunningAccount Where CompanyId=c.Id And IOTypeId=2 And Convert(nchar(10),TradeDate,120)=@ShengChengDate),0) As WKSR--尾款收入
			,getdate()
			,@ShengChengDate
			From Company c Where c.Id=@CompanyId
		end
End
	RETURN
